#region - Class details -
/*  
 *  Developer   : Wicus Botha
 *  Description : 
 *  History     : WB - 2007-12-11 - Created
 */
#endregion

using System;
using System.Collections;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using IQ.Base.Services;
using IQ.DbBase;

namespace SqlServerAdmin
{
	/// <summary>
	/// Description of SqlServer.
	/// </summary>
	/// 	

	public class SqlQuery
	{
		#region # Constructor # 
	 	public SqlQuery()
		{
	 		tableStats.Add("ResultCount",0);
		}
		#endregion
		
		#region - Private variables - 
        //private string currentSql;
        private string lastError;
        #endregion
		
		//#region - Private methods - 
		//#endregion

		#region - Protected variables -  
        private DataTable resultTable = new DataTable();
		protected IDictionary<string,int> tableStats = new Dictionary<string,int>();
		#endregion
				
		//#region - Protected methods - 		
		//#endregion
		
		#region - Public properties / variables - 		
		public int ResultCount
		{
			get {return tableStats["ResultCount"];}
		}
        public string LastError
        {
            get { return lastError; }
            set { lastError = value; }
        }
        public DataTable ResultTable
        {
            get { return resultTable; }
            set { resultTable = value; }
        }
		#endregion

		#region - Public methods - 
		public void RunQuery(string sql)
		{
            //currentSql = sql;
            resultTable = DbHelper.Load_Table(sql);
            if (resultTable.Columns[0].ColumnName.ToLower() != "error")
			{
				resultTable = DbHelper.Load_Table(sql);
				tableStats["ResultCount"] = resultTable.Rows.Count;
			}
			else
				this.lastError = "No SQL connection assigned to object.";				
		}
        public void FilterData(int colIndex, string operatorStr, string filterValue)
        {
            DbUtil.FilterData(resultTable, colIndex, operatorStr, filterValue);
        }
        public void FilterData(string colName, string filterValue)
        {
            DbUtil.FilterDataTable(resultTable, colName, filterValue);
        }
        #endregion
	}	
	
	public class SqlTable : SqlQuery
	{
		#region # Constructor # 
		private void Constructor()
		{
			tableStats.Add("RowCount",0);
			tableStats.Add("TableSize",0); // Size in KB - MB = KB/1024, GIG = KB /1024/1024
			tableStats.Add("IndexSize",0);
            forceFullTable = false;
			fullTable = false;	
		}		
	 	public SqlTable() : base()
		{
	 		this.Constructor();
		}
		#endregion
		
		// Constants
		private const int rowLimit = 1000;
		private const int sizeLimit = 1024; // 1 Meg		
		#region - Private variables - 
		// Property variables
		private string name;
        private bool forceFullTable;
		private bool fullTable;
		private Hashtable fieldList = new Hashtable();
		private DataTable tableFields = new DataTable();
		private DataTable tableKeys = new DataTable();
		private DataTable tableKeysFields = new DataTable();
        private string[] pkFields;
        private string[] uqFields;
        #endregion 
		
		#region - Private methods - 
		private void loadTableStats()
		{
			DataTable table = new DataTable();
            table = DbHelper.Load_Table(SqlQueries.SqlTablesDetail + " AND OBJ.Name = '" + this.name + "'");
			if (table.Rows.Count >0 )
			{
                if (table.Columns.IndexOf("RowsCount") > 0)
				    tableStats["RowCount"] = int.Parse(table.Rows[0]["RowsCount"].ToString());
                if (table.Columns.IndexOf("TableSizeKB") > 0)
                    tableStats["TableSize"] = (int)table.Rows[0]["TableSizeKB"];
                if (table.Columns.IndexOf("IndexSizeKB") > 0)
                    tableStats["IndexSize"] = (int)table.Rows[0]["IndexSizeKB"];
			}
		}
		private void loadTableMetaData()
		{
			// Load Table Fields & Keys
            this.tableFields = DbHelper.Load_Table(SqlQueries.GetSqlTableFields(this.name));
            this.tableKeys = DbHelper.Load_Table(SqlQueries.GetSqlTableKeys(this.name));
            this.tableKeysFields = DbHelper.Load_Table(SqlQueries.GetSqlTableKeysFields(this.name));
            if (tableKeys.Rows.Count > 0 && tableKeys.Columns.IndexOf(SqlQueries.Field_IndexId) >= 0)
                this.FilterKeysData((short)tableKeys.Rows[0][SqlQueries.Field_IndexId]);
            pkFields = get_IndexFields(SqlQueries.IndexStatusPk);
            uqFields = get_IndexFields(SqlQueries.IndexStatusUnique);
		}
        private int getIndexId(short indexType)
        {
            if (tableKeys == null)
                this.loadTableMetaData();
            foreach (DataRow dr in tableKeys.Rows)
            {
                if ((Convert.ToInt32(dr[SqlQueries.Field_IndexStatus]) & indexType) != 0)
                    return Convert.ToInt32(dr[SqlQueries.Field_IndexId]);
            }
           return -1;
        }
        private string[] get_IndexFields(short indexType)
        {
            string[] lstToLoad = null;
            int indexId = getIndexId(indexType);
            if (indexId >= 0)
            {
                DataRow[] drFields = tableKeysFields.Select(SqlQueries.Field_IndexId + "=" + indexId);
                if (drFields != null)
                {
                    lstToLoad = new string[drFields.Length];
                    for (int i = 0; i < drFields.Length; ++i)
                    {
                        lstToLoad[i] = drFields[i][SqlQueries.Field_ColumnName].ToString();
                    }
                }
            }
            return lstToLoad;
        }
        private string getWhereFieldList(DataRow dataRow, string[] fldList)
        {
            string tmpWHERE = "";
            foreach (string field in fldList)
            {
                // PK cannot be null
                tmpWHERE += field + "=" +
                 DbUtil.GetDefaultFor(DbUtil.GetFieldType(dataRow.Table.Columns[field].DataType.ToString()),
                        dataRow[field, DataRowVersion.Original].ToString()) + " AND ";
            }
            return tmpWHERE;
        }
        private string getWhereClause(DataRow dataRow, bool tryfirstPk, DataRowVersion rowVersion)
        {
            string tmpWHERE = "";
            if (tryfirstPk && pkFields != null && pkFields.Length > 0)
            {
                tmpWHERE = getWhereFieldList(dataRow, pkFields);
            }
            else if (uqFields != null && uqFields.Length > 0)
            {
                tmpWHERE = getWhereFieldList(dataRow, uqFields);
            }
            else if (pkFields != null && pkFields.Length > 0)
            {
                tmpWHERE = getWhereFieldList(dataRow, pkFields);
            }
            else
            {
                foreach (DataColumn col in ResultTable.Columns)
                {
                    if (dataRow.IsNull(col, rowVersion))
                        tmpWHERE += col.ColumnName + " IS NULL AND ";
                    else
                        tmpWHERE += col.ColumnName + "=" + DbUtil.GetDefaultFor(DbUtil.GetFieldType(col.DataType.ToString()),
                            dataRow[col, rowVersion]) + " AND ";
                }
            }
            if (tmpWHERE.Length > 5)
               tmpWHERE = tmpWHERE.Remove(tmpWHERE.Length - 5, 5); // remove last " AND "
            return tmpWHERE;
        }
        private string getUpdateStatement(DataRow dataRow, bool tryfirstPk, bool fullUpdate)
		{			
			string tmpSET = "";
            string tmpWHERE = getWhereClause(dataRow, tryfirstPk, DataRowVersion.Original);
            bool buildWhere = (tmpWHERE == "");
            for (int i = 0; i < ResultTable.Columns.Count; i++)
			{
                DataRow fieldRecord = tableFields.Select(SqlQueries.Field_ColumnName + "='" + ResultTable.Columns[i].ColumnName + "'").FirstOrDefault();
                if (fieldRecord != null && ((short)fieldRecord[SqlQueries.Field_ColumnIsIdentity] != 1))
                {
                    // Build SET clause
                    if (fullUpdate ||
                        (dataRow[i, DataRowVersion.Original].ToString() != dataRow[i, DataRowVersion.Current].ToString()))
                    {
                        tmpSET = tmpSET + ResultTable.Columns[i].ColumnName + "=";
                        if (dataRow.IsNull(i))
                            tmpSET = tmpSET + "NULL,";
                        else
                            tmpSET = tmpSET + DbUtil.GetDefaultFor(DbUtil.GetFieldType(ResultTable.Columns[i].DataType.ToString()),
                                dataRow[i, DataRowVersion.Current].ToString()) + ",";
                    }
                }
			}
			if (tmpSET.Length > 0)
			{
				tmpSET = tmpSET.Remove(tmpSET.Length-1,1); // remove last ","
				return "UPDATE " + this.name + " SET " + tmpSET + " WHERE " + tmpWHERE;
			}
			else
				return "";
		}
        private string getInsertStatement(DataRow dataRow)
		{
			string tmpFIELDS = "";
			string tmpVALUES = "";
			bool keyInsert = false;
			bool autoInc = false;
            bool emptyRow = true;
			string defaultVal = "";
			for (int i = 0; i < ResultTable.Columns.Count; i++)
			{				
                if (emptyRow && !dataRow[i].Equals(DBNull.Value))
                    emptyRow = false;
				// Find field details record
				DataRow KeyRecord = tableFields.Select(SqlQueries.Field_ColumnName+ "='" + ResultTable.Columns[i].ColumnName +"'")[0];
				if (!dataRow.IsNull(i))  // Value is NOT null
				{
                    if (!KeyRecord.IsNull(SqlQueries.Field_ColumnIsIdentity) && !keyInsert)
                        keyInsert = ((short)KeyRecord[SqlQueries.Field_ColumnIsIdentity] == 1);
					// Field value is not null, Add Field & Value
					tmpFIELDS += ResultTable.Columns[i].ColumnName + ",";
					tmpVALUES = tmpVALUES + DbUtil.GetDefaultForString(dataRow[i].ToString()) + ",";
				}
				else // Value is NULL
				{
					if (KeyRecord != null)
					{
                        autoInc = ((Int16)KeyRecord[SqlQueries.Field_ColumnIsIdentity] == 1);
						defaultVal = KeyRecord[SqlQueries.Field_ColumnDefaultValue].ToString();
					}
					// If the fields is an auto increment then exclude the field
					// so that the DB can add the value
					if (!autoInc)
					{
						tmpFIELDS += ResultTable.Columns[i].ColumnName + ",";
						if (defaultVal == "")
							tmpVALUES += "NULL,";
						else
							tmpVALUES += defaultVal + ",";
					}
				}		
			}
			tmpFIELDS = tmpFIELDS.Remove(tmpFIELDS.Length-1,1);
			tmpVALUES = tmpVALUES.Remove(tmpVALUES.Length-1,1);
			string insertSQL = "INSERT INTO " + this.name + "(" + tmpFIELDS + ")" +
					" VALUES (" + tmpVALUES + ")";
			if (keyInsert)
				insertSQL = "SET IDENTITY_INSERT " + this.name + " ON " + insertSQL +
							"SET IDENTITY_INSERT " + this.name + " OFF" ;
            if (emptyRow)
                return "";
            else
			    return insertSQL;
		}
		private string getDeleteSatement(DataRow dataRow, bool tryfirstPk)
		{
            string tmpWHERE = getWhereClause(dataRow, tryfirstPk, DataRowVersion.Original);
			return "DELETE FROM " + this.name + " WHERE " + tmpWHERE;
		}
		#endregion
		
		#region - Public properties - 
		public string Name
		{			
			get {return this.name;}
			set {
					if (DbHelper.CanConnect)
					{
						this.name = value;						
						this.loadTableMetaData();
					}
					else
						this.LastError = "No SQL connection assigned to object.";
				}
		}
		public int RowCount
		{
			get {return tableStats["RowCount"];}
		}		
		public int TableSize
		{
			get {return tableStats["TableSize"];}
		}		
		public int IndexSize
		{
			get {return tableStats["IndexSize"];}
		}
        public bool ForceFullTable
        {
            get { return forceFullTable; }
            set { forceFullTable = value; }
        }
		public bool FullTable
		{
			get {return fullTable;}
		}		
		public Hashtable FieldList
		{
			get {return this.fieldList;}
		}
		public DataTable TableFields
		{
			get {return this.tableFields;}
		}
		public DataTable TableKeys
		{
			get {return this.tableKeys;}
		}
		public DataTable TableKeyFields
		{
			get {return this.tableKeysFields;}
		}
        public string[] UniqueFields
        {
            get { return uqFields; }
        }
		#endregion
		
		#region - Public methods - 	
        public void LoadTableStats()
        {
            loadTableStats();
        }
        public void LoadTableMetaData()
        {
            loadTableMetaData();
        }
        public void OpenTableData()
        {
            loadTableStats();
            if (forceFullTable)
                fullTable = forceFullTable;
            else
                fullTable = (this.RowCount < rowLimit || this.TableSize < sizeLimit);
            string tmpSQL;
            if (fullTable)
                tmpSQL = "SELECT * FROM " + name;
            else
                tmpSQL = "SELECT TOP " + rowLimit.ToString() + " * FROM " + name;
            // Load Table
            this.RunQuery(tmpSQL);
        }
		public int GetChangeCount()
		{
			if (this.ResultTable.GetChanges() != null)
				return this.ResultTable.GetChanges().Rows.Count;
			else
				return 0;
		}
		public bool ApplyUpdates()
		{
			LastError = "";			
			string aLine = "";
            SqlCommand cmd = (SqlCommand)DbHelper.GetConnection().CreateCommand();
            //
			for (int i = 0; i < ResultTable.Rows.Count; i++)
			{
				DataRow dr = ResultTable.Rows[i];
				aLine = "";
				switch (ResultTable.Rows[i].RowState)
                {
					case DataRowState.Added : aLine = getInsertStatement(ResultTable.Rows[i]); break;
                    case DataRowState.Deleted: aLine = getDeleteSatement(ResultTable.Rows[i], true); break;
                    case DataRowState.Modified: aLine = getUpdateStatement(ResultTable.Rows[i], true, false); break;
				}
				//	
				if (aLine != String.Empty)
				{
					try {
						//debug
						//tmpSQL = tmpSQL + aLine + "\n";
                        cmd.CommandText = aLine;
						if (cmd.Connection.State != ConnectionState.Open)
							cmd.Connection.Open();
						//
						int iCount = cmd.ExecuteNonQuery();
					}				 
					catch (Exception e) {
						LastError = e.Message;
                        Logging.Error("SqlTable - apply update error: " + e.Message);
                        return false;
					}
				}
			}
			// debug
            //return tmpSQL;
            return true;
        }
		public void FilterKeysData(int keyIndex)
		{			
			if (keyIndex == -1)
				this.tableKeysFields.DefaultView.RowFilter = "";
			else
			{
				this.tableKeysFields.DefaultView.RowFilter = SqlQueries.Field_IndexId +"=" + keyIndex.ToString();
			}				
		}
        public string EmptyTable()
        {
            string result = DbHelper.ExecuteSql_rStr("TRUNCATE TABLE " + this.name);
            if (result != "")
                result = DbHelper.ExecuteSql_rStr("DELETE FROM " + this.name);
            return result;
        }
        public void SetTableNameFromSql(string sql)
        {
            string tmpSql = sql.Replace(System.Environment.NewLine, " ").Replace("\n", " ");
            int fromPos = tmpSql.ToUpper().IndexOf(" FROM ") + " FROM ".Length;
            string tmpName = tmpSql.Substring(fromPos, sql.Length - fromPos);
            if (tmpName.IndexOf(" ") > 0)
                tmpName = tmpName.Remove(tmpName.IndexOf(" "));
            if (tmpName != name)
            {
                name = tmpName;
                this.loadTableMetaData();
            }
        }
        public string GetWhereClause(DataRow dataRow, bool tryfirstPk)
        {
            return getWhereClause(dataRow, tryfirstPk, DataRowVersion.Original);
        }
        public string GetInsertStatement(DataRow dataRow)
        {
            return getInsertStatement(dataRow);
        }
        public string GetUpdateStatement(DataRow dataRow, bool tryfirstPk)
        {
            return getUpdateStatement(dataRow, tryfirstPk, true);
        }
        public string GetDeleteSatement(DataRow dataRow, bool tryfirstPk)
        {
            return getDeleteSatement(dataRow, true);
        }
		#endregion
	}	
}

